// ActionScript file
import flash.data.SQLConnection;
import flash.events.SQLEvent;
import flash.filesystem.File;

import model.Chapter;
import model.Character;

[Bindable]
public var currentProjectFile:String = "";

[Bindable]
public var currentProjectTitle:String = "";

[Bindable]
public var currentProjectAuthor:String = "";

[Bindable]
public var currentEditObject:*;

public var database:File = File.documentsDirectory.resolvePath("BookManager");

public var connection:SQLConnection;

public var sql:String;

public var statement:SQLStatement;

public function createNewProject(title:String, author:String):void{
	var projectName:String = title.toLowerCase().replace(/\s/gi, "").replace(/[^\w \xC0-\xFF]/g, "");
	database = File.documentsDirectory.resolvePath("BookManager/" + projectName + ".db");	
	if (!database.exists) createDatabaseFile(projectName);	
	
	if (connection) connection.close();
	
	connection = new SQLConnection();
	connection.addEventListener(SQLEvent.OPEN, onDatabaseOpen);
	connection.addEventListener(SQLErrorEvent.ERROR, errorHandler);
	connection.openAsync(database);
	
	setLastProject(projectName);
}

private function projectCreated(event:SQLEvent):void{
	sql = "UPDATE preferences SET lastProject='" + currentProjectTitle + "'";
	
	statement = new SQLStatement();
	statement.addEventListener(SQLErrorEvent.ERROR, errorHandler);
	statement.sqlConnection = connection;
	statement.text = sql;
	statement.execute();
}

private function createDatabaseFile(name:String):void{
	database.parent.createDirectory();
	database.resolvePath(name + ".db");	
}

public function loadProjectFromFile(file:File):void{
	database = file;
	
	if (connection) connection.close();
	
	connection = new SQLConnection();
	connection.addEventListener(SQLEvent.OPEN, onDatabaseOpen);
	connection.addEventListener(SQLErrorEvent.ERROR, errorHandler);
	connection.openAsync(database);
}

private function onDatabaseOpen(event:SQLEvent):void{
	createInfoTable();
}

private function createInfoTable():void{
	sql = "CREATE TABLE IF NOT EXISTS info (id INTEGER PRIMARY KEY AUTOINCREMENT, titel TEXT, author TEXT)";
		
	statement = new SQLStatement();
	statement.addEventListener(SQLEvent.RESULT, createChapterTable);
	statement.addEventListener(SQLErrorEvent.ERROR, errorHandler);
	statement.sqlConnection = connection;
	statement.text = sql;
	statement.execute();
}

private function createChapterTable(event:SQLEvent):void{
	sql = "CREATE TABLE IF NOT EXISTS chapters (id INTEGER PRIMARY KEY AUTOINCREMENT, contents TEXT, title TEXT, metadata TEXT)";
		
	statement = new SQLStatement();
	statement.addEventListener(SQLEvent.RESULT, createCharacterTable);
	statement.addEventListener(SQLErrorEvent.ERROR, errorHandler);
	statement.sqlConnection = connection;
	statement.text = sql;
	statement.execute();
}

private function createCharacterTable(event:SQLEvent):void{
	sql = "CREATE TABLE IF NOT EXISTS characters (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, description TEXT, alias TEXT, isMajor BOOLEAN, background TEXT, chapters TEXT, events TEXT, outlines TEXT, items TEXT, locations TEXT, image TEXT)";
		
	statement = new SQLStatement();
	statement.addEventListener(SQLEvent.RESULT, createEventTable);
	statement.addEventListener(SQLErrorEvent.ERROR, errorHandler);
	statement.sqlConnection = connection;
	statement.text = sql;
	statement.execute();
}

private function createEventTable(event:SQLEvent):void{
	sql = "CREATE TABLE IF NOT EXISTS events (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, items TEXT, characters TEXT, description TEXT, significance INTEGER, relatedEvents TEXT, chapters TEXT)";
		
	statement = new SQLStatement();
	statement.addEventListener(SQLEvent.RESULT, createItemTable);
	statement.addEventListener(SQLErrorEvent.ERROR, errorHandler);
	statement.sqlConnection = connection;
	statement.text = sql;
	statement.execute();
}

private function createItemTable(event:SQLEvent):void{
	sql = "CREATE TABLE IF NOT EXISTS items (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, description TEXT, image TEXT)";
		
	statement = new SQLStatement();
	statement.addEventListener(SQLEvent.RESULT, createLocationTable);
	statement.addEventListener(SQLErrorEvent.ERROR, errorHandler);
	statement.sqlConnection = connection;
	statement.text = sql;
	statement.execute();
}

private function createLocationTable(event:SQLEvent):void{
	sql = "CREATE TABLE IF NOT EXISTS locations (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, description TEXT, background TEXT, image TEXT)";
		
	statement = new SQLStatement();
	statement.addEventListener(SQLEvent.RESULT, createNoteTable);
	statement.addEventListener(SQLErrorEvent.ERROR, errorHandler);
	statement.sqlConnection = connection;
	statement.text = sql;
	statement.execute();
}

private function createNoteTable(event:SQLEvent):void{
	sql = "CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY AUTOINCREMENT, text TEXT)";
		
	statement = new SQLStatement();
	statement.addEventListener(SQLEvent.RESULT, createOutlineTable);
	statement.addEventListener(SQLErrorEvent.ERROR, errorHandler);
	statement.sqlConnection = connection;
	statement.text = sql;
	statement.execute();
}

private function createOutlineTable(event:SQLEvent):void{
	sql = "CREATE TABLE IF NOT EXISTS outlines (id INTEGER PRIMARY KEY AUTOINCREMENT, timeline TEXT, name TEXT, events TEXT, scenes TEXT)";
		
	statement = new SQLStatement();
	statement.addEventListener(SQLEvent.RESULT, createSceneTable);
	statement.addEventListener(SQLErrorEvent.ERROR, errorHandler);
	statement.sqlConnection = connection;
	statement.text = sql;
	statement.execute();
}

private function createSceneTable(event:SQLEvent):void{	
	sql = "CREATE TABLE IF NOT EXISTS scenes (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, time TEXT, locations TEXT, characters TEXT, items TEXT, events TEXT)";
		
	statement = new SQLStatement();
	statement.addEventListener(SQLEvent.RESULT, finishedCreatingTables);
	statement.addEventListener(SQLErrorEvent.ERROR, errorHandler);
	statement.sqlConnection = connection;
	statement.text = sql;
	statement.execute();
}

private function finishedCreatingTables(event:SQLEvent):void{
	loadLastEditScreen();
}



private function loadLastProject(name:String):void{
	database = File.documentsDirectory.resolvePath("BookManager/" + name + ".db");	
		
	if (connection) connection.close();
	
	connection = new SQLConnection();
	connection.addEventListener(SQLEvent.OPEN, onDatabaseOpen);
	connection.addEventListener(SQLErrorEvent.ERROR, errorHandler);
	connection.openAsync(database);
}


public function saveChapter(chapter:Chapter):void{
	statement = new SQLStatement();
	
	if (!chapter.id){
		sql = "INSERT INTO chapters (title, contents, metadata) VALUES ('" + chapter.title + "', '" + chapter.contents + "', '" + chapter.metadata + "')";
		statement.addEventListener(SQLEvent.RESULT, objectAdded);		
	}
	else {		
		sql = "UPDATE chapters SET title='" + chapter.title +"', contents='" + chapter.contents + "', metadata='" + chapter.metadata + "'  WHERE id=" + chapter.id;
		statement.addEventListener(SQLEvent.RESULT, objectSaved);
	}
	
	currentEditObject = chapter;
	
	statement.addEventListener(SQLErrorEvent.ERROR, errorHandler);
	statement.sqlConnection = connection;
	statement.text = sql;
	statement.execute();
}

private function objectAdded(event:SQLEvent):void{
	var sqlresult:SQLResult = statement.getResult(); 
	currentEditObject.id = sqlresult.lastInsertRowID;
	statement.removeEventListener(SQLEvent.RESULT, objectAdded);	
}

private function objectSaved(event:SQLEvent):void{
	statement.removeEventListener(SQLEvent.RESULT, objectSaved);
}

public function saveCharacter(character:Character):void{
	statement = new SQLStatement();
	
	if (!character.id){
		sql = "INSERT INTO characters (name, description, alias, isMajor, background, chapters, events, outlines, items, locations, image)"
		sql += " VALUES ('" + character.name + "', '" + character.description + "', '" + character.alias + "', '" + character.isMajor + "', '" + character.background + "', '" + character.chapters + "', '" + character.events + "', '" + character.outlines + "', '" + character.items + "', '" + character.locations + "', '" + character.image + "')";
		trace(sql);
		statement.addEventListener(SQLEvent.RESULT, objectAdded);		
	}
	else {		
		sql = "UPDATE chapters SET name='" + character.name +"', " + 
			  "description='" + character.description + "', " + 
			  "alias='" + character.alias + "', " +
			  "isMajor='" + character.isMajor + "'" +  
			  "  WHERE id=" + character.id;
		statement.addEventListener(SQLEvent.RESULT, objectSaved);
	}
	
	currentEditObject = character;
	
	statement.addEventListener(SQLErrorEvent.ERROR, errorHandler);
	statement.sqlConnection = connection;
	statement.text = sql;
	statement.execute();
}

private function errorHandler(event:SQLErrorEvent):void{
	if (event.text == "Error #3125: Unable to open the database file.") resetPreferences();
	else {
		mx.controls.Alert.show(event.error + ": " + event.text);
	}	
}



